﻿using Abp.Domain.Repositories;
using Abp.Extensions;
using Abp.UI;
using DHJ.Cost.EntityFrameworkCore;
using DHJ.Cost.InitData.Dto;
using DHJ.Cost.ManHours;
using DHJ.Cost.Tasks;
using DHJ.Cost.Timing;
using DHJ.Cost.Workshops;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace DHJ.Cost.InitData
{
    public class InitDataAppService : CostAppServiceBase, IInitDataAppService
    {
        private readonly string connectionString = @"Server=10.20.253.11; Database=ProductBarCodeManagementAndTrack; User=sa; Password=tjhj123456,;";
        private readonly string manHourConnectionString = @"Server=10.20.254.32; Database=DHJ_MonthlyPlan; User=sa; Password=tjhj123456,;";

        private readonly IRepository<Batch, string> _batchRepository;

        private readonly IRepository<Task, long> _taskRepository;
        private readonly IRepository<BatchManHour> _manHourRepository;

        public InitDataAppService(IRepository<Batch, string> batchRepository, IRepository<Task, long> taskRepository, IRepository<BatchManHour> manHourRepository)
        {
            _batchRepository = batchRepository;
            _taskRepository = taskRepository;
            _manHourRepository = manHourRepository;
        }
        public async System.Threading.Tasks.Task<SyncResultDto> InitTasks()
        {
            var result = new SyncResultDto();
            DateTime startTime = DateTime.Now;
            DatabaseHelper db = new DatabaseHelper(connectionString);
            DbCommand cmd = db.GetStoredProcCommond(
                @"SELECT [ID] as [SysId]
                            ,[TaskNum] as [TaskNum]
	                        ,[ProductModel] as [ModelId]
                            ,[ProductName] as [ProductName]
                            ,[DrawingNum] as [ProductId]
                            ,[PlanAmount] as [PlannedQuantity]
                            ,[MakeAmount] as [ProductionQuantity]
                    FROM [ProductBarCodeManagementAndTrack].[dbo].[Task]
                    WHERE [Enable] = 1");

            DataTable dt = db.ExecuteDataTable(cmd);

            var tasks = await _taskRepository.GetAll().ToListAsync();

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    var taskNum = dr["TaskNum"]?.ToString();
                    var productId = dr["ProductId"]?.ToString();
                    var modelId = dr["ModelId"]?.ToString() == null ? "-" : dr["ModelId"]?.ToString();
                    var barcodeSysId = dr["SysId"]?.ToString();
                    var productName = dr["ProductName"]?.ToString();
                    var plannedQuantity = Convert.ToInt32(dr["PlannedQuantity"]);
                    var productionQuantity = Convert.ToInt32(dr["ProductionQuantity"]);


                    var task = tasks.FirstOrDefault(e => e.TaskNum == taskNum.Trim()
                    && e.ProductId == productId.Trim());

                    if (task != null)
                    {
                        task.SysId = barcodeSysId;
                        task.ModelId = modelId;
                        task.ProductName = productName;
                        task.PlannedQuantity = plannedQuantity;
                        task.ProductionQuantity = productionQuantity;

                        result.UpdateAmount++;
                    }
                    else
                    {
                        task = new Task(barcodeSysId, taskNum, modelId,
                            productId, productName,
                            plannedQuantity,
                            productionQuantity);

                        await _taskRepository.InsertAsync(task);
                        result.CreateAmount++;
                    }
                }
                catch (Exception)
                {
                    result.ErrorAmount++;
                    continue;
                }


            }

            result.TimeStr = (DateTime.Now - startTime).ToString("c");

            return result;
        }

        public async System.Threading.Tasks.Task<SyncResultDto> InitBzBatches()
        {
            var result = new SyncResultDto();
            DateTime startTime = DateTime.Now;
            DatabaseHelper db = new DatabaseHelper(connectionString);
            DbCommand cmd = db.GetStoredProcCommond(
                @"SELECT task.[ID] as [SysId]
                    ,barcode.[BarcodeFull] as [Id]
                    ,barcode.[TotalNumber] as [Quantity]
                    ,barcode.[ZhiKongCardNum] as [QcCard]
                    ,task.[ID] as [TaskId]
                    ,task.[DrawingNum] as [ProductId]
                    ,parent.[BarcodeFull] as [ParentId]
                    ,parent.[TotalNumber] as [ParentQuantity]
              	    ,0 as [HasSub]
                FROM [ProductBarCodeManagementAndTrack].[dbo].[User_BZ_BarCode] as barcode LEFT JOIN [ProductBarCodeManagementAndTrack].[dbo].[Task] as task
                	ON barcode.[TaskID] = task.[ID]  LEFT JOIN [ProductBarCodeManagementAndTrack].[dbo].[User_BZ_BarCode] as parent
                	ON barcode.[PID] = parent.[ID]
                WHERE barcode.[Enable] = 1
                ORDER BY barcode.[ID]");

            DataTable dt = db.ExecuteDataTable(cmd);

            var list = new List<DataRow>();

            var tasks = await _taskRepository.GetAll().ToListAsync();
            var batches = await _batchRepository.GetAll().ToListAsync();

            foreach (DataRow dr in dt.Rows)
            {

                if (list.Any(e => e["Id"].ToString() == dr["Id"].ToString()))
                {
                    continue;
                }

                list.Add(dr);
                try
                {
                    var sysId = dr["SysId"]?.ToString();
                    var id = dr["Id"]?.ToString();
                    var quantity = Convert.ToInt32(dr["Quantity"]);
                    var qcCard = dr["QcCard"]?.ToString();
                    var taskId = Convert.ToInt64(dr["TaskId"]);
                    var parentId = dr["ParentId"]?.ToString();
                    int? parentQuantity = null;
                    if (!dr["ParentQuantity"].ToString().IsNullOrEmpty())
                        parentQuantity = Convert.ToInt32(dr["ParentQuantity"]);

                    var task = tasks.First(e => e.SysId == sysId);
                    var batch = batches.FirstOrDefault(e => e.Id == id);

                    if (batch != null)
                    {
                        batch.Quantity = quantity;
                        batch.QcCard = qcCard;
                        batch.TaskId = task.Id;
                        batch.ParentId = parentId;
                        batch.ParentQuantity = parentQuantity;

                        result.UpdateAmount++;
                    }
                    else
                    {
                        batch = new Batch(id, task.Id, quantity, qcCard, parentId, parentQuantity);

                        await _batchRepository.InsertAsync(batch);
                        result.CreateAmount++;
                    }
                }
                catch (Exception e)
                {
                    result.ErrorAmount++;
                    continue;
                }


            }

            result.TimeStr = (DateTime.Now - startTime).ToString("c");

            return result;
        }

        public async System.Threading.Tasks.Task<SyncResultDto> InitBatches()
        {
            var result = new SyncResultDto();
            DateTime startTime = DateTime.Now;
            DatabaseHelper db = new DatabaseHelper(connectionString);
            DbCommand cmd = db.GetStoredProcCommond(
                @"SELECT task.[ID] as [SysId]
                      ,barcode.[BarcodeFull] as [Id]
                      ,barcode.[TotalNumber] as [Quantity]
                      ,barcode.[QcCard]
                	  ,task.[ID] as [TaskId]
                	  ,task.[DrawingNum] as [ProductId]
                	  ,parent.[BarcodeFull] as [ParentId]
                	  ,parent.[TotalNumber] as [ParentQuantity]
                	  ,barcode.[HasSub] as [HasSub]
                FROM [ProductBarCodeManagementAndTrack].[dbo].[BarCode] as barcode LEFT JOIN [ProductBarCodeManagementAndTrack].[dbo].[Task] as task
                	  ON barcode.[TaskID] = task.[ID]  LEFT JOIN [ProductBarCodeManagementAndTrack].[dbo].[BarCode] as parent
                	  ON barcode.[PID] = parent.[ID]
                WHERE barcode.[Enable] = 1
                ORDER BY barcode.[ID]");

            DataTable dt = db.ExecuteDataTable(cmd);

            var list = new List<DataRow>();
            var tasks = await _taskRepository.GetAll().ToListAsync();
            var batches = await _batchRepository.GetAll().ToListAsync();

            foreach (DataRow dr in dt.Rows)
            {

                if (list.Any(e => e["Id"].ToString() == dr["Id"].ToString()))
                {
                    continue;
                }

                list.Add(dr);
                try
                {
                    var sysId = dr["SysId"]?.ToString();
                    var id = dr["Id"]?.ToString();
                    var quantity = Convert.ToInt32(dr["Quantity"]);
                    var qcCard = dr["QcCard"]?.ToString();
                    var taskId = Convert.ToInt64(dr["TaskId"]);
                    var parentId = dr["ParentId"]?.ToString();
                    int? parentQuantity = null;
                    if (!dr["ParentQuantity"].ToString().IsNullOrEmpty())
                        parentQuantity = Convert.ToInt32(dr["ParentQuantity"]);

                    var task = tasks.First(e => e.SysId == sysId);
                    var batch = batches.FirstOrDefault(e => e.Id == id);

                    if (batch != null)
                    {
                        batch.Quantity = quantity;
                        batch.QcCard = qcCard;
                        batch.TaskId = task.Id;
                        batch.ParentId = parentId;
                        batch.ParentQuantity = parentQuantity;
                        result.UpdateAmount++;
                    }
                    else
                    {
                        batch = new Batch(id, task.Id, quantity, qcCard, parentId, parentQuantity);

                        await _batchRepository.InsertAsync(batch);
                        result.CreateAmount++;
                    }
                }
                catch (Exception e)
                {
                    result.ErrorAmount++;
                    continue;
                }


            }

            result.TimeStr = (DateTime.Now - startTime).ToString("c");

            return result;
        }

        public async System.Threading.Tasks.Task<SyncResultDto> SyncManHours()
        {
            var result = new SyncResultDto();
            DateTime startTime = DateTime.Now;
            DatabaseHelper db = new DatabaseHelper(manHourConnectionString);
            DbCommand cmd = db.GetStoredProcCommond(
                @"SELECT task.[Id] as [SysId]
                	  ,main.[Dep]
                      ,task.[BarCode]
                      ,task.[BarAmount]
                      ,task.[HappenHour]
                FROM [DHJ_MonthlyPlan].[dbo].[WorkTaskSet] as task left join [DHJ_MonthlyPlan].[dbo].[WorkTaskMainSet] as main
                on task.[WorkTaskMainId] = main.[Id]
                where task.[HappenHour] <> 0 and main.[yearNum] = " + DateTime.Now.Year + " and main.[monthNum] = " + DateTime.Now.Month);

            DataTable dt = db.ExecuteDataTable(cmd);

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    var sysId = dr["SysId"]?.ToString();
                    var dept = (Workshop)Convert.ToInt32(dr["Dep"]);
                    var barCode = dr["BarCode"]?.ToString();
                    var barAmount = Convert.ToInt32(dr["BarAmount"]);
                    var hours = Convert.ToDecimal(dr["HappenHour"]);


                    var mh = await _manHourRepository.FirstOrDefaultAsync(e => e.BatchId == barCode);

                    if (mh != null)
                    {
                        mh.Hours.Standard = hours;

                        result.UpdateAmount++;
                    }
                    else
                    {
                       mh = new BatchManHour(new OccurrenceTime(DateTime.Now.Year, DateTime.Now.Month), dept, barCode, 1, new ManHour(hours));

                        await _manHourRepository.InsertAsync(mh);
                        result.CreateAmount++;
                    }
                }
                catch (Exception e)
                {
                    result.ErrorAmount++;
                    continue;
                }


            }

            result.TimeStr = (DateTime.Now - startTime).ToString("c");

            return result;
        }

        public async System.Threading.Tasks.Task Demo()
        {
            var tasks = await _batchRepository.CountAsync();
        }
    }
}
